package com.stm.bi.core.util;

import com.stm.bi.dto.query.DimensionDataGroupQry;
import com.stm.bi.dto.query.GroupField;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @author zhouyuanpeng
 * @date 2025-03-11 10:59
 */
public class SqlUtils {

    private static final Logger logger = LoggerFactory.getLogger(SqlUtils.class);

    public static String buildSql(DimensionDataGroupQry dimensionDataGroupQry , String tableName){

        List<String> fields = dimensionDataGroupQry.getGroupBy();
        List<GroupField> groupFields = dimensionDataGroupQry.getGroupFields();

        StringBuilder fieldStr = new StringBuilder();
        String groupStr = "";
        String groups = "";

        if(groupFields != null && !groupFields.isEmpty()){
            if(fields != null && !fields.isEmpty()){
                groups += String.join(",", fields);
            }

            for(GroupField gf: groupFields){
                fieldStr.append(gf.getGroupType())
                        .append("(")
                        .append(gf.getFieldName())
                        .append(") as ")
                        .append(gf.getFieldName())
                        .append(",");
            }

            fieldStr.append(groups);
            // 删除多余逗号
            if(groups.isEmpty()){
                fieldStr.deleteCharAt(fieldStr.length() - 1);
            }else {
                // 拼接分支
                groupStr = " group by " + groups;
            }
        }else {
            fieldStr.append(" * ");
        }


        StringBuilder queryStr = new StringBuilder();

        // 企业组织编码
        if (dimensionDataGroupQry.getOrgNo() != null){
            queryStr.append(" org_no = '").append(dimensionDataGroupQry.getOrgNo()).append("'");
        }

        if (dimensionDataGroupQry.getStartDate() != null && dimensionDataGroupQry.getEndDate() != null){
            queryStr.append(" and date >= '").append(dimensionDataGroupQry.getStartDate()).append("' and date <= '").append(dimensionDataGroupQry.getEndDate()).append("'");
        }else if(dimensionDataGroupQry.getStartDate() != null && dimensionDataGroupQry.getEndDate() == null){
            queryStr.append(" and date >= '").append(dimensionDataGroupQry.getStartDate()).append("'");
        }else if(dimensionDataGroupQry.getEndDate() != null && dimensionDataGroupQry.getStartDate() == null){
            queryStr.append(" and date <= '").append(dimensionDataGroupQry.getEndDate()).append("'");
        }

        // 时间类型
        if(dimensionDataGroupQry.getDateType() != null){
            queryStr.append(" and date_type = '").append(dimensionDataGroupQry.getDateType()).append("'");
        }
        // 销售类型
        if(dimensionDataGroupQry.getSalesType() != null){
            queryStr.append(" and sales_type = '").append(dimensionDataGroupQry.getSalesType()).append("'");
        }

        // 部门
        if (dimensionDataGroupQry.getDeptId() != null){
            queryStr.append(" and dept_id = ").append(dimensionDataGroupQry.getDeptId());
        } else if (dimensionDataGroupQry.getDeptIds() != null && !dimensionDataGroupQry.getDeptIds().isEmpty()){
            queryStr.append(" and dept_id in ('").append(String.join("','", dimensionDataGroupQry.getDeptIds().stream().map(Objects::toString).toList())).append("')");
        }

        // 其他特殊查询条件
        if(dimensionDataGroupQry.getOtherConditions() != null && !dimensionDataGroupQry.getOtherConditions().isEmpty()){
            Map<String, Object> map = dimensionDataGroupQry.getOtherConditions();
            for (String s : map.keySet()) {
                if(map.get(s) instanceof List && !((List) map.get(s)).isEmpty()){
                    List<String> stringList = ((List<?>) map.get(s)).stream().map(Objects::toString).toList();
                    queryStr.append(" and ").append(s).append(" in ('").append(String.join("','", stringList)).append("')");
                }else {
                    queryStr.append(" and ").append(s).append(" = '").append(map.get(s)).append("'");
                }
            }
        }

        // 删除多余的and
        if (queryStr.toString().startsWith(" and")) {
            queryStr.delete(0, 4);
        }

        //排序
        String orderStr = "";
        if(dimensionDataGroupQry.getOrderBy() != null){
            orderStr = " order by " + dimensionDataGroupQry.getOrderBy();
            if(dimensionDataGroupQry.getOrderType() != null){
                orderStr = orderStr + " " + dimensionDataGroupQry.getOrderType();
            }
        }

        // 分页
        String limitStr = "";
        if(dimensionDataGroupQry.getLimit() != null){
            limitStr = " limit " + dimensionDataGroupQry.getLimit().getLeft() + "," + dimensionDataGroupQry.getLimit().getRight() ;
        }
        String sql = "select " + fieldStr + " from " + tableName + " where " + queryStr + groupStr + orderStr + limitStr;
        ;
        logger.info("生成语句： " + sql);
        return sql;
    }
}
